#include "QtMySQL.h"
#include "ui_QtMySQL.h"

QtMySQL::QtMySQL(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::QtMySQL)
{
    ui->setupUi(this);

    //标志
    int i = 0;
    //添加数据库驱动
    QSqlDatabase database;
    QString databaseName;
    if (i == 0){
        //SQLite数据库
        database = QSqlDatabase::addDatabase("QSQLITE");
        databaseName = ":memory:";
    }else{
        //MySQL数据库
        database = QSqlDatabase::addDatabase("QMYSQL");
        databaseName = "test";
        database.setHostName("localhost");//主机名
        database.setDatabaseName(databaseName);//数据库名
        database.setUserName("root");//用户名
        database.setPassword("chench");//密码
    }

    //判断连接数据库是否成功
    if(!database.open()){
        qDebug() << "连接数据库失败：" << database.lastError().text();
        return ;
    }
    qDebug() << "创建数据库[" << databaseName << "]成功";


    //数据库查询
    QSqlQuery query;

    //判断表格是否存在
    if (i == 1){// SQLite不用检查表格是否存在
        i = 0;
        query.exec ("select 1 as len from information_schema. tables where table_schema=(select database()) and table_name='person'");
        while (query.next()) {
            i = query.value("len").toInt();
        }
    }
    if (i == 0){// 表格不存在就创建，并初始化插入数据
        //创建表格
        query.exec("create table person (id int primary key, firstname varchar(20), lastname varchar(20))");
        qDebug() << "表格创建成功！";
        //批量预处理语句
        // ? 占位符（ODBC）
        query.prepare("insert into person(id, firstname, lastname) values(?, ?, ?)");
        //参数
        QVariantList ID, firstname, lastname;
        ID << 101 << 102 << 103;
        firstname << "chen" << "fan" << "zhai";
        lastname << "changhang" << "chen" << "wenhao";
        //占位符绑定参数
        query.addBindValue(ID);
        query.addBindValue(firstname);
        query.addBindValue(lastname);

        //批量执行
        query.execBatch();
        qDebug() << "批量预处理插入数据成功";

        //查询表格
        QString selectSql = "select id, firstname, lastname from person";
        query.exec(selectSql);
        qDebug() << "查询表格：" << selectSql ;
        while (query.next()) {
            qDebug() << query.value("ID").toInt()
                     << query.value("firstname").toString()
                     << query.value("lastname").toString();
        }

    }

    //创建表格模型，并设置数据库表格
    tableModel = new QSqlTableModel(this);
    tableModel->setTable("person");
    //显示数据
    ui->tableView->setModel(tableModel);
    tableModel->select();
    //设置表头
    tableModel->setHeaderData(0, Qt::Horizontal, "ID");
    tableModel->setHeaderData(1, Qt::Horizontal, "firstname");
    tableModel->setHeaderData(2, Qt::Horizontal, "lastname");
    //设置手动提交
    tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    //设置不允许编辑
//    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);

}

QtMySQL::~QtMySQL()
{
    delete ui;
}



void QtMySQL::on_btn_submit_clicked()
{
    //提交
    tableModel->submitAll();
}

void QtMySQL::on_btn_revert_clicked()
{
    //撤销
    tableModel->revertAll();
    tableModel->submitAll();
}

void QtMySQL::on_btl_insert_clicked()
{
    //插入
    //生成空行
    QSqlRecord record = tableModel->record();
    //获取行数
    int row = tableModel->rowCount();
    //添加空行
    tableModel->insertRecord(row, record);
}

void QtMySQL::on_btn_delete_clicked()
{
    //删除
    //获取选中行
    QItemSelectionModel *selectionModel= ui->tableView->selectionModel();
    //获取选中索引
    QModelIndexList indexList = selectionModel->selectedRows();
    //删除选中行
    for(int i = 0; i < indexList.size(); i++)
    {
        tableModel->removeRow(indexList.at(i).row());
    }
}

void QtMySQL::on_btn_select_clicked()
{
    //获取输入
    QString keywords = ui->le_keywords->text();
    if (keywords.isEmpty()){
        QMessageBox::warning(this, "提示", "请输入id过滤！");
        tableModel->setFilter("");//重置过滤条件
        tableModel->select();//查询
    }else{
        QString filter = QString("id = '%1'").arg(keywords);
        tableModel->setFilter(filter);//设置过滤条件
        tableModel->select();//查询
    }
}


